<?xml version="1.0"?>
<OnTheFlyInstruction xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <InstanceId>CSOM - Get All Items Table</InstanceId>
  <Tags>
    <string>SharePoint Client</string>
    <string>CSOM</string>
  </Tags>
  <AdditionalAssemblies />
  <Code><![CDATA[// ----------------------
//  Get all items from a SharePoint list and save as a DataTable
// ----------------------

using System;
using System.Data;
using System.Linq;
using System.Net;
using xSolon.Instructions.DTO;
using Microsoft.SharePoint.Client;
using xSolon.Instructions;
using xSolon.SP.ManagedClient;

public class SP14Client_Sample : AbstractInstruction
{
	public override void Run()
	{
		var ctx = new SP14ClientHelper().GetContext("http://localhost"
			,SP14AuthenticationType.Windows, this.CredentialCache.First());
	
		var list = ctx.Web.Lists.GetByTitle("Profiles");
		
		var items = list.GetItems(CamlQuery.CreateAllItemsQuery());
		
		ctx.Load(items);
		
		ctx.ExecuteQuery();
		
		//ResultTable = GetTableOfSerializedValues(items);
		
		ResultTable = GetTable(items);
		
		var asString = false;
		if (asString)
		{
			ctx.Load(items, each => each.Include(item => item, item => item.FieldValuesAsText));
			ctx.ExecuteQuery();
			ResultTable = GetTableOfStrings(items);
		}
	}
	
	// XML Serialiation of values
	// Useful to know value type
	// Very slow
	DataTable GetTableOfSerializedValues(ListItemCollection items)
	{
		var table = new DataTable();

        foreach (ListItem li in items)
        {
            var row = table.NewRow();

            foreach (var kv in li.FieldValues)
            {
                var key = kv.Key;

                if (!table.Columns.Contains(key))
                {
                    table.Columns.Add(key);
                }

                var value1 = "";
                if (kv.Value != null)
                {
                    value1 = kv.Value.RawPrint();
                }

                row[key] = value1;
            }

            table.Rows.Add(row);
        }

        table.AcceptChanges();

        return table;
	}
	
	DataTable GetTable(ListItemCollection items)
    {
        var table = new DataTable();

        foreach (ListItem li in items)
        {
            var row = table.NewRow();

            foreach (var kv in li.FieldValues)
            {
                var key = kv.Key;

                if (!table.Columns.Contains(key))
                {
                    table.Columns.Add(key);
                }

                var value1 = "";
                if (kv.Value != null)
                {
                    value1 = kv.Value.ToString();
                }
				
                if (kv.Value is FieldLookupValue)
                {
                    value1 = (kv.Value as FieldLookupValue).ToString();
                }
                else if (kv.Value is FieldUrlValue)
                {
                    value1 = string.Format("<a href='{0}'>{1}</a>", (kv.Value as FieldUrlValue).Url, (kv.Value as FieldUrlValue).Description);
                }
                else if (kv.Value is string[])
                {
                	value1 = kv.Value.RawPrint();
                	
                }

                row[key] = value1;
            }

            table.Rows.Add(row);
        }

        table.AcceptChanges();

        return table;
    }

    DataTable GetTableOfStrings(ListItemCollection items)
    {
        var table = new DataTable();

        foreach (ListItem li in items)
        {
            var row = table.NewRow();

            foreach (var kv in li.FieldValuesAsText.FieldValues)
            {
                var key = kv.Key;

                if (!table.Columns.Contains(key))
                {
                    table.Columns.Add(key);
                }

                var value1 = "";
                if (kv.Value != null)
                {
                    value1 = kv.Value.ToString();
                }
				/*
                if (kv.Value is FieldLookupValue)
                {
                    value1 = (kv.Value as FieldLookupValue).LookupValue;
                }
                else if (kv.Value is FieldUrlValue)
                {
                    value1 = string.Format("<a href='{0}'>{1}</a>", (kv.Value as FieldUrlValue).Url, (kv.Value as FieldUrlValue).Description);
                }
                else if (kv.Value is string[])
                {
                	value1 = kv.Value.RawPrint();
                	
                }
*/
                row[key] = value1;
            }

            table.Rows.Add(row);
        }

        table.AcceptChanges();

        return table;
    }

}]]></Code>
</OnTheFlyInstruction>